package com.elite.groovy.callback


import com.elite.groovy.util.EliteBean
import com.fasterxml.jackson.databind.JsonNode
import com.fasterxml.jackson.databind.ObjectMapper
import org.apache.commons.logging.Log
import org.apache.commons.logging.LogFactory
import org.springframework.jdbc.core.JdbcTemplate

import javax.servlet.http.HttpServletRequest
import javax.servlet.http.HttpServletResponse
/**
 * @projectName: Groovy
 * @package: com.elite.groovy.callback
 * @className: apiDynSql
 * @description: 信创DYNSQL表验证
 * @author: Rick
 * @date: 2024-08-16 11:13
 * @version: 1.0
 */

public class apiDynSql implements ServiceBackInvoke {
    private static final Log log = LogFactory.getLog(apiDynSql.class);

    public String doCallBack(EliteBean eliteBean, HttpServletRequest request, HttpServletResponse response) {

        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/json;charset=UTF-8");
        long startTime = System.currentTimeMillis();
        String Check_sql = "", SqlType = "", CheckStr = "", DYNID = "", DYNID2 = "", DYNTYPE = "", StrDynSqlJSON = "";
        int Check_Status = 0;

        JdbcTemplate jt = new JdbcTemplate(eliteBean.getDataSource());
        //清理数据
        jt.update("Delete From Xc_Checkdyn_Sql");
        //扫描动态页面
        String DynSql = '''Select Tds.Dynid, Td.Dynid2, Td.Dyntype, Replace(Replace(Tds.Content, '\n', ' '), '\t', ' ') As Dynsqlinfo
                              From Tab_Dyn Td
                              Left Join Tab_Dyn_Sqls Tds
                                On Tds.Dynid = Td.Dynid
                             Where 1 = 1
                               And Length(Tds.Content) > 10'''

        List<Map<String, Object>> PustSqlList = jt.queryForList(DynSql);


        for (int i = 0; i < PustSqlList.size(); i++) {
            DYNID = PustSqlList.get(i).get("Dynid");
            DYNID2 = PustSqlList.get(i).get("Dynid2");
            DYNTYPE = PustSqlList.get(i).get("Dyntype");
            StrDynSqlJSON = PustSqlList.get(i).get("Dynsqlinfo");


            log.debug("正在处理动态页面第" + i + "个：" + DYNID);
            //jSON遍历解析
            ObjectMapper objectMapper = new ObjectMapper();

            JsonNode rootNode = objectMapper.readTree(StrDynSqlJSON);

            // 解析并打印所有的SQL语句
            rootNode.fields().forEachRemaining(entry -> {
                String key = entry.getKey();
                JsonNode value = entry.getValue();
                String contentJson = value.get("content").asText();
                String content = value.get("content").asText();


                //获取前6位字符并转为小写
                if (content.length() > 10) {
                    SqlType = content.substring(0, 10);
                    SqlType = SqlType.toLowerCase();
                    log.debug("原始SQL: " + content);
                    //循环替换50次
                    for (int j = 0; j < 50; j++) {
                        String str1 = "{DSL_" + j + "}";
                        String str2 = "@{C" + j + "}N";
                        String str3 = "@{C" + j + "}S";
                        String str4 = "@{C" + j + "}L";
                        String str5 = "@{C" + j + "}@";
                        String str6 = "@{C" + j + "}D";
                        String str7 = "to_date(@{C" + j + "}S";
                        content = content.replace(str1, " ")
                                .replace(str7, "to_date('2024-08-19 00:00:00'")
                                .replace(str2, "1")
                                .replace(str3, "'a'")
                                .replace(str4, "'a','b'")
                                .replace(str5, " ")
                                .replace(str6, "'2024-08-16 00:00:00'")
                                .replace("{C_EPIDTB}", " ")
                                .replace("{C_BY}", "000034")
                                .replace("{C_GRP}", "SYSTEM")
                                .replace("{C_NOW}", "2024-08-16 00:00:00")
                                .replace("{C_LANG}", "0")
                                .replace("{C_GUID}", "0000000000000000000")
                                .replace("{DSL_213}", " ")
                                .replace("{DSL_212}", " ")
                                .replace("{DSL_211}", " ")
                                .replace("{DSL_210}", " ")
                                .replace("{DSL_214}", " ")
                                .replace("{DSL_215}", " ")
                                .replace("{C_PIDTB}", " ")

                    }
                    Check_sql = content.replace("@{", "'").replace("}S", "'");
                    log.debug("执行SQL: " + Check_sql);

                } else {
                    SqlType = "";
                }

                if (SqlType.indexOf("select") > -1 || SqlType.indexOf("insert") > -1 || SqlType.indexOf("update") > -1 || SqlType.indexOf("delete") > -1) {

                    try {
                        jt.execute(Check_sql);
                        CheckStr = "可以执行";
                        Check_Status = 1;
                    } catch (Exception e) {
                        CheckStr = e.toString();
                        log.debug("执行错误" + Check_sql);
                        Check_Status = 0;
                    }
                    if (CheckStr.length() > 1000) {
                        CheckStr = CheckStr.substring(0, 1000)
                    }
                    String UUID = UUID.randomUUID().toString();
                    Date currentDate = new Date();
                    String CheckSql = ''' insert into xc_checkdyn_sql(Check_guid,dyn_id,sql_json,Check_sql,Check_Result,Check_Status,createddate,DYNTYPE,DYNID2)values(?,?,?,?,?,?,?,?,?) '''
                    jt.update(CheckSql, UUID, DYNID, contentJson, Check_sql, CheckStr, Check_Status, currentDate, DYNTYPE, DYNID2)

                }

            });

            log.debug("处理完成：" + DYNID);

        }


    }

}




